13.2.1. Read Committed Isolation Level

Replace "because of the above rules" and the example with the following:-


Because of the above rules, transactions can produce incorrect values if the FOR UPDATE clause is not included on any SELECT statement that is used to select data that will subsequently be used to UPDATE.  In the following example, a stock purchase transaction B of 100 units happens during a stock sale transaction A of 50 units.  However, transaction A overwrites the update made by transaction B because it did not lock product 123 using SELECT ... FOR UPDATE, so the resulting quantiy_on_hand is wrong.

Statement
 Transaction A -- stock sale
 Transacton B -- stock purchase
1
 SELECT quantity_on_hand INTO qoh FROM products WHERE product_id = 123;
-- now qoh = 200, say.  Client checks qoh > 50 so stock is available.
2
UPDATE products SET quantity_on_hand = quantity_on_hand + 100 WHERE product_id = 123;
3
COMMIT;
4
 UPDATE products SET quantity_on_hand = qoh - 50;
5
 COMMIT;
-- now product 123 quantity_on_hand = 150, not 250



The current docs say that statement 2 is OK, which implies that the Update statement is atomic, i.e. that no other transaction can split the read part of the update from the write part.  I suspect that that is not true, and no mention should be made in the docs unless it is really known to be true.


13.2.2. Repeatable Read Isolation Level

Before the paragraph "The Repeatable Read mode provides" add

These concurrent update errors can be minimized (if not avoided entirely) by adding the FOR UPDATE clause to any SELECT statements for any rows that are retrieved and likely to be updated.  That will cause the transaction to wait until it can obtain and exclusive lock on the row, and then prevent any other transaction from reading the row.